﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[_UpdateOrder] 
	
AS
BEGIN
	declare @srq DATETIME,@erq datetime
set @srq=Convert(datetime,Convert(varchar(10),getdate(),120) +' 00:00:00')
set @erq=Convert(datetime,Convert(varchar(10),getdate(),120) +' 23:59:59')

;WITH nl as (
select * from (
select count(1) as a ,OrderId from [Order] where financerenlingdate>=@srq and financerenlingdate<=@erq
and orderid<>''
 group by OrderId
) as b
where a>1
),
nl2 as (

select id,orderid from [Order] where orderid in (select orderid from nl)
),
nl3 as (
select max(id) id,orderid,left(orderid,8)+
(case len((Convert(int,right(orderid,4))+1)) when 1 then '000' when 2 then '00' when 3 then '0' else '' end)+Convert(varchar,Convert(int,right(orderid,4))+1)
as new_orderid
  from nl2 group by orderid
)

update [Order] set orderid=new_orderid from nl3,[Order] where [Order].id=nl3.id 
and financerenlingdate>=@srq and financerenlingdate<=@erq

END
